#!/usr/bin/python
#-*- coding:utf-8 -*-

# store.py
#
# Copyright (c) 2007 Marcelo Lira dos Santos
#
# Author: Marcelo Lira dos Santos <setanta@gmail.com>
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU Lesser General Public License
# as published by the Free Software Foundation; either version 2 of
# the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public
# License along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
# USA

import gtk
import sqlite3 as sqlite
from os.path import join

import data

SKILL_POINTS = 1
WEAPON_POINTS = 2

def get_image_pixbuf(image_file):
    if not image_file:
        return None
    else:
        return gtk.gdk.pixbuf_new_from_file(image_file)

class Store:
    def __init__(self, datapath, images_path):
        self.datapath = datapath
        self.images_path = images_path

        self.conn_sys = sqlite.connect(join(self.datapath, 'taggen.db')) #DB_SYSTEM
        self.cur_sys = self.conn_sys.cursor()

        self.conn_usr = sqlite.connect(join(self.datapath, 'taggen.db')) #DB_USER
        self.cur_usr = self.conn_usr.cursor()


    # Generic -----------------------------------------------------
    def __get_name(self, id, table):
        if id is None or table is None:
            return None

        query = "SELECT name FROM %s WHERE id=%s" % (table, str(id))
        self.cur_sys.execute(query)
        result = self.cur_sys.fetchone()

        if len(result) == 0:
            return None
        else:
            return result[0]

    def __get_list(self, table, columns=''):
        if table is None:
            return None
        query = "SELECT id, name%s FROM %s" % (columns, table)
        cursor = self.cur_sys.execute(query)
        return cursor.fetchall()


    # Gods --------------------------------------------------------
    def get_gods_list(self):
        return self.__get_list('gods', ', realm')

    def get_god_name(self, id):
        return self.__get_name(id, 'gods')


    # Places ------------------------------------------------------
    def get_places_list(self):
        places = self.__get_list('places', ', parent_id')
        parent = {}
        for i in xrange(len(places)):
            id, name, parent_id = places[i]
            if parent_id == -1:
                parent[id] = [name]
            else:
                try:
                    parent[parent_id].append('%s, %s' % \
                                             (name, parent[parent_id][0]))
                except KeyError:
                    parent[id] = [name]
        places = []
        map(places.extend, parent.values())
        return places

    def get_place_name(self, id):
        return self.__get_name(id, 'places')


    # Timeline ----------------------------------------------------
    def get_timeline(self):
        query = "SELECT year, event FROM timeline"
        cursor = self.cur_sys.execute(query)
        timeline = {}

        for year, event in cursor.fetchall():
            timeline[year] = event

        return timeline


    # Races --------------------------------------------------------
    def get_races_list(self):
        return self.__get_list('races')

    def get_race_name(self, id):
        return self.__get_name(id, 'races')

    def get_race(self, id):
        query = "SELECT * FROM races WHERE id=" + str(id)
        self.cur_sys.execute(query)
        race_row = self.cur_sys.fetchone()

        query = "SELECT p.id FROM race_profession rp, professions p "\
                "WHERE p.id=rp.profession_id AND rp.race_id=" + str(id)
        self.cur_sys.execute(query)
        available_prof_rows = self.cur_sys.fetchall()

        def build_object(row, av_prof_rows):
            if row is None:
                return None
            race = data.Race(row[1], row[0])
            race.image = get_image_pixbuf(join(self.images_path, row[2]))
            for i in range(7):
                race.attribute_bonus[i] = row[i+3]
            race.base_speed = row[10]
            race.ef = row[11]
            race.base_height = row[12]
            race.base_weight = row[13]
            race.age = (row[14], row[15])
            race.available_professions = \
                 [av_prof[0] for av_prof in av_prof_rows]
            return race

        return build_object(race_row, available_prof_rows)


    # Professions --------------------------------------------------
    def get_professions_list(self):
        return self.__get_list('professions')

    def get_profession_name(self, id):
        return self.__get_name(id, 'professions')

    def get_profession(self, id):
        query = "SELECT * FROM professions WHERE id=" + str(id)
        self.cur_sys.execute(query)

        def build_object(row):
            if row is None:
                return None
            profession = data.Profession(row[1], row[0])
            profession.image = get_image_pixbuf(join(self.images_path, row[2]))
            profession.description = row[3]
            profession.posessions = (row[4], 0, 0)
            profession.eh = row[5]
            profession.skill_points = row[6]
            profession.weapon_points = row[7]
            profession.combat_points = row[8]
            profession.penalized_skill_group = row[9]
            profession.specialized_skill = row[10]
            profession.attribute_for_magic = row[11]
            profession.spell_group = row[12]
            return profession

        return build_object(self.cur_sys.fetchone())

    # Specializations ----------------------------------------------
    def get_specializations_list(self):
        return self.__get_list('specializations')

    def get_specialization_name(self, id):
        return self.__get_name(id, 'specializations')

    def get_specialization(self, id):
        if id is None:
            return None

        query = "SELECT * FROM specializations WHERE id=" + str(id)
        self.cur_sys.execute(query)

        def build_object(row):
            if row is None:
                return None
            special = data.Specialization(row[1], row[0])
            special.description = row[2]
            special.profession = row[3]
            special.spell_group = row[4]
            special.technique_group = row[5]
            return special

        return build_object(self.cur_sys.fetchone())


    # Skills --------------------------------------------------------
    def get_skills_list(self):
        return self.__get_list('skills')

    def get_skill(self, id):
        query = "SELECT * FROM skills WHERE id=" + str(id)
        self.cur_sys.execute(query)

        def build_object(row):
            if row is None:
                return None
            skill = data.Skill(row[1], row[0])
            skill.description = row[2]
            skill.bonus = row[3]
            skill.has_specialization = bool(row[4])
            skill.specialization_suggestions = \
                  self.get_skill_specialization_suggestions(row[0])
            return skill

        return build_object(self.cur_sys.fetchone())

    def get_skill_name(self, id):
        return self.__get_name(id, 'skills')

    def __get_skill_group_list(self, parent_id='-1'):
        query = "SELECT id, name FROM skill_groups "\
                "WHERE parent_id=%s" % parent_id
        self.cur_sys.execute(query)
        return self.cur_sys.fetchall()

    def get_skill_group_parents(self):
        return self.__get_skill_group_list()

    def get_skill_group_children(self, parent_id):
        return self.__get_skill_group_list(parent_id)

    def get_skills_from_group(self, skillgroup_id):
        query = "SELECT s.id, s.name, sg.cost, s.bonus, s.has_specialization"\
                " FROM skills s, skill2group sg"\
                " WHERE s.id=sg.skill_id AND sg.skillgroup_id=" + \
                str(skillgroup_id)
        self.cur_sys.execute(query)
        return self.cur_sys.fetchall()

    def get_skill_group_name(self, id):
        return self.__get_name(id, 'skill_groups ')

    def get_skill_specialization_suggestions(self):
        query = "SELECT skill_id, suggestion FROM " \
                "skill_specialization_suggestions ORDER BY suggestion"
        self.cur_sys.execute(query)
        return self.cur_sys.fetchall()


    # Spells --------------------------------------------------------
    def get_spells_list(self):
        return self.__get_list('spells')

    def get_spell(self, id):
        query = "SELECT * FROM spells WHERE id=" + str(id)
        self.cur_sys.execute(query)

        def build_object(row):
            if row is None:
                return None
            spell = data.Spell(row[1])
            spell.description = row[2]
            spell.evocation = row[3]
            spell.range = row[4]
            spell.duration = row[5]
            return spell

        return build_object(self.cur_sys.fetchone())

    def get_spell_name(self, id):
        return self.__get_name(id, 'spells')

    def get_spell_group_parents(self):
        query = "SELECT id, name FROM spell_groups "\
                "WHERE parent_id=-1"
        self.cur_sys.execute(query)
        return self.cur_sys.fetchall()

    def get_spell_group_children(self, parent_id):
        query = "SELECT id, name FROM spell_groups "\
                "WHERE parent_id=" + str(parent_id)
        self.cur_sys.execute(query)
        return self.cur_sys.fetchall()

    def get_spells_from_group(self, spellgroup_id):
        query = "SELECT s.id, sg.cost FROM spells s, spell2group sg "\
                "WHERE s.id=sg.spell_id AND sg.spellgroup_id=" + \
                str(spellgroup_id)
        self.cur_sys.execute(query)
        result = self.cur_sys.fetchall()

        return [(id, self.get_spell(id), cost) for id, cost in result]


    # Equipment -----------------------------------------------------
    def get_equipment_list(self):
        return self.__get_list('equipment')

    def get_equipment_item(self, id):
        query = "SELECT * FROM equipment WHERE id=" + str(id)
        self.cur_sys.execute(query)

        def build_object(row):
            if row is None:
                return None

            is_weapon = (row[6] == 1)
            is_defense = (row[7] == 1)

            if is_weapon:
                if is_defense:
                    item = data.Shield(row[2])
                else:
                    item = data.Weapon(row[2])
            elif is_defense:
                item = data.Defense(row[2])
            else:
                item = data.Item(row[2])


            if is_weapon:
                query = "SELECT * FROM equipment_weapons WHERE id=" + str(id)
                self.cur_sys.execute(query)
                wrow = self.cur_sys.fetchone()

                item.skill_id = wrow[1]
                item.min_strength = wrow[2]
                item.min_damage = wrow[3]
                item.attack_defense['l'] = wrow[4]
                item.attack_defense['m'] = wrow[5]
                item.attack_defense['p'] = wrow[6]
                item.range = wrow[7]

            if is_defense:
                query = "SELECT * FROM equipment_defense WHERE id=" + str(id)
                self.cur_sys.execute(query)
                drow = self.cur_sys.fetchone()

                item.type = drow[1]
                item.base_defense = drow[2]
                item.absorption = drow[3]

            item.group = row[1]
            item.description = row[3]
            item.price = row[5]

            image_file = row[4]
            if image_file is not None:
                item.image = get_image_pixbuf(join(self.images_path,
                                                   image_file))
            return item

        return build_object(self.cur_sys.fetchone())

    def get_equipment_groups(self):
        return self.__get_list('equipment_groups')

    def get_equipment_from_group(self, group_id):
        query = "SELECT id, name, description, price FROM equipment " \
                "WHERE group_id=" + str(group_id)
        self.cur_sys.execute(query)
        return self.cur_sys.fetchall()


    # Persona  -----------------------------------------------------
    def get_persona_list(self):
        query = "SELECT id, name, race, profession, level FROM persona"
        cursor = self.cur_usr.execute(query)
        return cursor.fetchall()

    def get_players_names(self):
        query = "SELECT player FROM persona"
        self.cur_usr.execute(query)
        result = set() # evita entradas repetidas
        for row in self.cur_usr.fetchall():
            if row[0] is not None:
                result.add(row[0])
        return list(result)

    def get_persona(self, id):
        query = "SELECT * FROM persona WHERE id=" + str(id)
        self.cur_usr.execute(query)

        def build_object(row):
            if row is None:
                return None
            persona = data.Persona(row[1], row[0])
            persona.player = row[2]
            persona.image = row[3]
            persona.attributes[data.INT] = row[4]
            persona.attributes[data.AUR] = row[5]
            persona.attributes[data.CAR] = row[6]
            persona.attributes[data.FOR] = row[7]
            persona.attributes[data.FIS] = row[8]
            persona.attributes[data.AGI] = row[9]
            persona.attributes[data.PER] = row[10]

            persona.active_defense = row[11]
            persona.passive_defense = row[12]
            persona.max_eh = row[13]
            persona.eh = row[14]
            persona.ef = row[15]
            persona.absorption = row[16]

            persona.race = self.get_race(row[17])
            persona.profession = self.get_profession(row[18])
            persona.specialization = self.get_specialization(row[19])
            persona.level = row[20]

            persona.skill_points = row[21]
            persona.weapon_points = row[22]
            persona.combat_points = row[23]
            persona.magic_points = row[24]

            persona.height = row[25]
            persona.weight = row[26]
            persona.age = row[27]

            persona.eyes = row[28]
            persona.hair = row[29]
            persona.skin = row[30]
            persona.appearance = row[31]

            persona.god = row[32]
            persona.social_class = row[33]
            persona.place_of_origin = row[34]
            persona.history = row[35]

            persona.copper_coins = row[36]
            persona.silver_coins = row[37]
            persona.gold_coins = row[38]

            persona.skills, persona.skills_specs = self.get_persona_skills(id)
            persona.spells = self.get_persona_spells(id)
            persona.equipment = self.get_persona_equipment(id)

            return persona

        return build_object(self.cur_usr.fetchone())

    def __get_persona_something(self, persona_id, table, columns):
        query = "SELECT %s FROM %s WHERE persona_id=%s" %\
                (columns, table, persona_id)
        self.cur_usr.execute(query)
        result = self.cur_usr.fetchall()
        dic = {}
        for key, value in result:
            dic[key] = value
        return dic

    def get_persona_skills(self, persona_id):
        # Habilidades comuns
        skills = self.__get_persona_something(persona_id, 'persona_skill',
                                              'skill_id, level')

        # Especializacoes
        query = "SELECT skill_id, specialization, level FROM "\
                "persona_skill_specialization WHERE persona_id=%s" % persona_id
        self.cur_usr.execute(query)
        result = self.cur_usr.fetchall()
        specs = {}
        for skill_id, specialization, level in result:
            if not specs.has_key(skill_id):
                specs[skill_id] = []
            specs[skill_id].append((specialization, level))

        return (skills, specs)

    def get_persona_spells(self, persona_id):
        return self.__get_persona_something(persona_id, 'persona_spell',
                                            'spell_id, level')

    def get_persona_equipment(self, persona_id):
        return self.__get_persona_something(persona_id, 'persona_item',
                                            'item_id, qtd')

    def save_persona(self, persona):
        if persona.id == -1:
            self.__insert_persona(persona)
        else:
            self.__update_persona(persona)

    def __insert_persona(self, persona):
        if persona.specialization is not None:
            spec_id = persona.specialization.id
        else:
            spec_id = -1
        print type(persona.copper_coins)
        print type(persona.silver_coins)
        print type(persona.gold_coins)
        print '"%s", "%s", %d, '\
              '%d, %d, %d' % \
              (persona.name, persona.player, persona.attributes[data.INT],
               persona.copper_coins, persona.silver_coins, persona.gold_coins)

        '''
        query = 'INSERT INTO persona ('\
                'name, player, att_intellect, '\
                'att_aura, att_charisma, att_strength,'\
                'att_physical, att_agility, att_perception, '\
                'active_defense, passive_defense, max_eh, eh, ef, '\
                'absorption, race, profession, specialization, level, '\
                'skill_points, combat_points, weapon_points, magic_points, '\
                'height, weight, age, eyes, hair, skin, appearance, '\
                'god, social_class, place_of_origin, history, '\
                'copper_coins, silver_coins, gold_coins '\
                ') VALUES ('\
                '"%s", "%s", %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, '\
                '%d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, "%s", "%s", '\
                '"%s", "%s", %d, "%s", "%s", "%s", %d, %d, %d)' % \
                (persona.name, persona.player, persona.attributes[data.INT],
                 persona.attributes[data.AUR], persona.attributes[data.CAR],
                 persona.attributes[data.FOR], persona.attributes[data.FIS],
                 persona.attributes[data.AGI], persona.attributes[data.PER],
                 persona.active_defense, persona.passive_defense,
                 persona.max_eh, persona.eh, persona.ef)#, persona.absorption,
                 persona.race.id, persona.profession.id, spec_id, persona.level,
                 persona.skill_points, persona.weapon_points,
                 persona.combat_points, persona.magic_points,
                 persona.height, persona.weight, persona.age, persona.eyes,
                 persona.hair, persona.skin, persona.appearance, persona.god,
                 persona.social_class, persona.place_of_origin, persona.history,
                 persona.copper_coins, persona.silver_coins, persona.gold_coins)
        '''
        query = 'INSERT INTO persona ('\
                'name, player, att_intellect, '\
                'att_aura, att_charisma, att_strength,'\
                'att_physical, att_agility, att_perception, '\
                'active_defense, passive_defense, max_eh, eh, ef, '\
                'absorption, race, profession, specialization, level, '\
                'skill_points, combat_points, weapon_points, magic_points, '\
                'age, height, weight, eyes, hair, skin, appearance, '\
                'god, social_class, place_of_origin, history, '\
                'copper_coins, silver_coins, gold_coins '\
                ') VALUES ('\
                '"%s", "%s", %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, '\
                '%d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, %d, "%s", "%s", '\
                '"%s", "%s", %d, "%s", "%s", "%s", %d, %d, %d)' %\
                (persona.name, persona.player, persona.attributes[data.INT],
                 persona.attributes[data.AUR], persona.attributes[data.CAR],
                 persona.attributes[data.FOR], persona.attributes[data.FIS],
                 persona.attributes[data.AGI], persona.attributes[data.PER],
                 persona.active_defense, persona.passive_defense,
                 persona.max_eh, persona.eh, persona.get_max_ef(), persona.absorption,
                 persona.race.id, persona.profession.id, spec_id, persona.level,
                 persona.skill_points, persona.weapon_points,
                 persona.combat_points, persona.magic_points,
                 persona.age, persona.height, persona.weight, persona.eyes,
                 persona.hair, persona.skin, persona.appearance, persona.god,
                 persona.social_class, persona.place_of_origin, persona.history,
                 persona.copper_coins, persona.silver_coins, persona.gold_coins)

        self.cur_usr.execute(query)
        self.conn_usr.commit()
        persona.id = last_insert_rowid()
        print 'persona.id: ',
        print persona.id

    def __update_persona(self, persona):
        if persona.specialization is not None:
            spec_id = persona.specialization.id
        else:
            spec_id = -1
        query = 'UPDATE persona SET name="%s", player="%s", '\
                'att_intellect=%d, att_aura=%d, att_charisma=%d, '\
                'att_strength=%d, att_physical=%d, att_agility=%d, '\
                'att_perception=%d, active_defense=%d, passive_defense=%d, '\
                'max_eh=%d, eh=%d, ef=%d, absorption=%d, race=%d, '\
                'profession=%d, specialization=%d, level=%d, '\
                'skill_points=%d, combat_points=%d, weapon_points=%d, '\
                'magic_points=%d, height=%d, weight=%d, age=%d, '\
                'eyes="%s", hair="%s", skin="%s", appearance="%s", '\
                'god=%d, social_class="%s", place_of_origin="%s", '\
                'history="%s", copper_coins=%d, silver_coins=%d, gold_coins=%d'\
                ' WHERE id=%d' % \
                (persona.name, persona.player, persona.attributes[data.INT],
                 persona.attributes[data.AUR], persona.attributes[data.CAR],
                 persona.attributes[data.FOR], persona.attributes[data.FIS],
                 persona.attributes[data.AGI], persona.attributes[data.PER],
                 persona.active_defense, persona.passive_defense,
                 persona.max_eh, persona.eh, persona.ef, persona.absorption,
                 persona.race.id, persona.profession.id, spec_id, persona.level,
                 persona.skill_points, persona.weapon_points,
                 persona.combat_points, persona.magic_points,
                 persona.height, persona.weight, persona.age, persona.eyes,
                 persona.hair, persona.skin, persona.appearance, persona.god,
                 persona.social_class, persona.place_of_origin, persona.history,
                 persona.copper_coins, persona.silver_coins, persona.gold_coins,
                 persona.id)

        print query
        self.cur_usr.execute(query)
        self.conn_usr.commit()

def test():
    store = Store()
    print 'races list'
    print store.get_races_list()
    print 'race[1]'
    race = store.get_race(1)
    print unicode(race)


if __name__ == '__main__':
    test()
